﻿using FirstTry.Model;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FirstTry.DB
{
    class DBBattery
    {
        DBLocation dbl = new DBLocation(); 
        DBConnection dbc = new DBConnection();
    
        public Battery getBattery(String ID)
        {
            SqlConnection con = dbc.GetConnection();
            string query = "SELECT ID, locationID, booked, charged FROM Battery WHERE ID =" + ID;
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataReader dr = cmd.ExecuteReader();
            Battery battery = new Battery();
            while (dr.Read())
            {
                Location l = new Location();

                battery.ID = Convert.ToInt32(dr["ID"]);
                l = dbl.GetCityByID(dr["locationID"].ToString());
                battery.location = l;
                battery.booked = (bool)dr["booked"];
                battery.charged = (bool)dr["charged"];

            }
            return battery;
        }

        public void addBattery(Battery b)
        {
            string query = "INSERT INTO Battery (locationID, booked, charged) VALUES (@locationID, @booked, @charged)";
            SqlConnection con = dbc.GetConnection();
            SqlCommand cmd = new SqlCommand(query, con);

            cmd.Parameters.AddWithValue("@locationID", b.location);
            cmd.Parameters.AddWithValue("@booked", b.booked);
            cmd.Parameters.AddWithValue("@charged", b.charged);
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
        }

        public List<Battery> getAllBattery()
        {
            List<Battery> batteryList = new List<Battery>();

            SqlConnection con = dbc.GetConnection();
            string query = "SELECT ID, locationID, booked, charged FROM Battery";
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                Battery battery = new Battery();
                battery.ID = Convert.ToInt32(dr["ID"]);
                Location l = dbl.GetCityByID(dr["locationID"].ToString());
                battery.location = l;
                battery.booked = (bool)dr["booked"];
                battery.charged = (bool)dr["charged"];

                batteryList.Add(battery);
            }
            return batteryList;
        }

        public void editBattery(Battery b)
        {
            SqlConnection con = dbc.GetConnection();
            string editSql = "UPDATE Battery SET locationID = @newLocationID, booked = @newBooked, charged = newCharged"  + "WHERE ID =" + b.ID;
            SqlCommand editCmd = new SqlCommand(editSql, con);

            editCmd.Parameters.AddWithValue("@newLocation", b.location.ID);
            editCmd.Parameters.AddWithValue("@newBooked", b.booked);
            editCmd.Parameters.AddWithValue("@newCharged", b.charged);

            try
            {
                editCmd.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                Console.WriteLine("Error: " + e.ToString());
            }
        }

        public void deleteBattery(Battery b)
        {
            SqlConnection con = dbc.GetConnection();
            string deleteSql = "DELETE FROM Battery" + "WHERE ID=@ID";
            SqlCommand deleteCmd = new SqlCommand(deleteSql, con);
            deleteCmd.Parameters.AddWithValue("@ID", b.ID);

            try
            {
                deleteCmd.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                Console.WriteLine("Error: " + e.ToString());
            }
        }

        public List<Battery> getBatteryByLocation(string ID)
        {
            List<Battery> batteryList = new List<Battery>();

            SqlConnection con = dbc.GetConnection();
            string query = "SELECT ID, location, booked, charged FROM Battery WHERE locationID =" + ID;
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                Battery battery = new Battery();
                Location loc = new Location();
                battery.ID = Convert.ToInt32(dr["ID"]);
                battery.location = dbl.GetCityByID(dr["locationID"].ToString()); ;
                battery.booked = (bool)dr["booked"];
                battery.charged = (bool)dr["charged"];

                batteryList.Add(battery);
            }
            return batteryList;
        }
    }
}
